How to modify a Crystal Report to use ADO instead of ODBC
Two report formats report directly to an ActiveX Data Objects (ADO) RecordSet object (producing a .xml file):
- Crystal ADO Report
- Microsoft ADO Report
Running these reports in EMu generates a .xml file with the data from the record set. The location of this file can vary, but typically it can be found in:
C:\Users\[your username]\AppData\Local\KESoftware\Reports\e[module name]
For example, a report run in the Parties module, will save the xmldata file to:
C:\Users\[your username]\AppData\Local\KESoftware\Reports\eparties
Note: It is important not to move the xmldata.xml
file as this will cause problems when sharing the report with other users.
Crystal reports which currently connect to an ODBC data source can be modified to use an ADO RecordSet:
- In the EMu module from which the Crystal Report is run, click Reports on the Home tab of the Ribbon to open the Reports box:
- Select the Crystal Report in the list and view the properties for the report by selecting .
In this example we select the List (A4) report.
- In the Report Properties box, select Crystal ADO Report from the Type drop list:
The fields for this report are:
Two tables are generated in this report.
- Click OK and run the report.
- Click OK to close the error message and again display the Report Properties for the Crystal Report you are modifying (Step 2 above).
- Click Download to save the report locally and open it in the Crystal Report Designer.
- Select Database>Set Datasource Location from the Menu bar.
- In the Replace with pane, expand Create New Connection and then expand ADO.NET (XML).
- In the ADO.NET (XML) box, locate and select the
xmldata.xml
file created when the report was run by clicking the ellipsis beside File Path, e.g.:Where is the xmldata.xml file?The location of this file can vary, but typically it can be found in:
C:\Users\[your username]\AppData\Local\KESoftware\Reports\e[module name]
For example, a report run in the Parties module, will save the xmldata file to:
C:\Users\[your username]\AppData\Local\KESoftware\Reports\eparties
Note: More details here.
- Click Finish to close the ADO.NET (XML) box and return to the Set Datasource Location box:
- To map the old ODBC Catalogue fields to the new Catalogue table, click ecatalog_csv in the Current Data Source pane and then click the row table in the Replace with pane.
- Click Update to display the Map Fields box:
Fields with the same name are mapped automatically:
- Uncheck the Match type check box to reveal more fields in the Unmapped Fields pane:
- Complete mapping fields in the Unmapped Fields pane.
- When all fields are mapped click OK to return to the Set Datasource Location box.
- Repeat the mapping process for all fields (in this example, mapping fields in the Subreport
CreatorD
_csv
to the ADOCreatorDetails
table). - When all fields have been mapped in all tables click Close to return to the Crystal design window.
- Refresh (
F5
) the report. - Click OK to open the Record Selection Formula Editor.
- Change the link key field used by the old ODBC table to the link key field referenced by the ADO RecordSet:
- Save the report.
- The final step is to save the modified report back to EMu:
- In EMu, repeat Steps 1 and 2 above to display the Report Properties box for the report:
- Select Upload, locate the report file you modified and saved and save it back to EMu.
The Crystal ADO Report can now be run.
The ADO record set will be created and an error will display (the report is looking for a .csv data source).
We now need to modify the Crystal Report.
The Set Datasource Location dialogue will display:
Next we map fields from the old ODBC data source to the new ADO RecordSet. In this example there are two tables to map and one sub-report.
The Update button will be enabled:
In this example we map ecatalogue_key
to ecatalogue_key
and irn
to irn
by selecting both fields to map and clicking Map.
As they are mapped, fields are moved to the Mapped Fields pane:
If you have a sub-report object, you may receive an error regarding sub-report links, e.g.:
If so:
The report should now work correctly.